//
//  SQLiteManager.m
//  ZhiHuDaily
//
//  Created by albert on 16/8/26.
//  Copyright © 2016年 albert. All rights reserved.
//

#import "SQLiteManager.h"
#import "JKDateTool.h"
#import "SDWebImagePrefetcher.h"
#import "NewsDetailModel.h"
#import "NetworkingTool.h"

@interface SQLiteManager ()

@end

static NSUInteger _storyIDIndex = 0;
static NSArray *_IDs;
static NSString *_progressText;

@implementation SQLiteManager
JKSingletonM(manager)

/** 打开数据库 */
- (void)openDB:(NSString *)dbName{
    // 3.1获取数据库文件存放的路径
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES).lastObject;
    NSString *filePath = [path stringByAppendingPathComponent:dbName];
    JKLog(@"%@", filePath);
    
    // 3.2创建数据库
    self.dbQueue = [FMDatabaseQueue databaseQueueWithPath:filePath];
    
    // 3.3创建表
    [self createTable];
}

/** 创建表格 */
- (void)createTable{
    // 1.拼接创建表的SQL语句
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS t_story ('storyID' INTEGER NOT NULL PRIMARY KEY, 'title' TEXT, 'isCollected' INTEGER, 'isLiked' INTEGER, 'storyDetail' TEXT, 'createTime' TEXT DEFAULT (datetime('now', 'localtime')));";
    
    NSString *createMoreListTableSQL = @"CREATE TABLE IF NOT EXISTS t_storyList ('ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'storyID' INTEGER, 'title' TEXT, 'date' INTEGER, 'isRead' INTEGER, 'isTop' INTEGER, 'isLatest' INTEGER, 'isCollected' INTEGER, 'list' TEXT, 'updateTime' TEXT, 'createTime' TEXT DEFAULT (datetime('now', 'localtime')));";
    
    // 2.执行SQL语句
    [self.dbQueue inDatabase:^(FMDatabase *db) {
        if ([db executeUpdate:createTableSQL withArgumentsInArray:nil]) {
            JKLog(@"创建表成功");
        }
        
        if ([db executeUpdate:createMoreListTableSQL withArgumentsInArray:nil]) {
            JKLog(@"创建表成功");
        }
    }];
}

#pragma mark - 外部调用方法
#pragma mark - ------------------增------------------
/** 缓存顶部topStories */
+ (void)cacheTopStories:(NSArray *)topStories date:(NSString *)date{
    // 从网络获取的的topStories的ID列表
    NSArray *arr = [topStories valueForKeyPath:@"id"];
    arr = [arr sortedArrayUsingComparator:^NSComparisonResult(id  _Nonnull obj1, id  _Nonnull obj2) {
        NSNumber *num1 = [NSNumber numberWithInt:[obj1 intValue]];
        NSNumber *num2 = [NSNumber numberWithInt:[obj2 intValue]];
        NSComparisonResult result = [num1 compare:num2];
        return result == NSOrderedDescending;
    }];
    // 从数据库取得的topStories的ID列表
    NSArray *topIDs = [SQLiteManager queryTopStoryIDs];
    
    // 比较两个列表是否完全相同，是的话直接返回，不需要进行缓存
    if (topIDs.count == arr.count) {
        BOOL allEqual = NO;
        for (int i = 0; i < arr.count; i++) {
            int arrNum = [arr[i] intValue];
            int topIDsNum = [topIDs[i] intValue];
            allEqual = (arrNum == topIDsNum);
        }
        if (allEqual) return;
    }
    
    // 如果有不相同的，删除全部topStories，重新缓存
    NSString *deleteSQL = @"delete from t_storyList where isTop = 1;";
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:deleteSQL withArgumentsInArray:nil];
    }];
    
    for (NSDictionary *dict in topStories) {
        NSString *storyID = dict[@"id"];
        NSString *title = dict[@"title"];
        // 将字典转为NSData
        NSData *listData = [NSJSONSerialization dataWithJSONObject:dict options:NSJSONWritingPrettyPrinted error:nil];
        
        // 将NSData转为字符串
        NSString *listStr = [[NSString alloc] initWithData:listData encoding:NSUTF8StringEncoding];
        
        // 拼接插入SQL语句
        NSString *insertSQL = @"INSERT OR IGNORE INTO t_storyList (storyID, title, isCollected, isRead, isTop, isLatest, list, date, updateTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
        
        [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
            [db executeUpdate:insertSQL withArgumentsInArray:@[storyID, title, @(0), @(0), @(1), @(1), listStr, date, @"0"]];
        }];
    }
}

/** 缓存首页数据 */
+ (void)cacheStoryLists:(NSArray *)stories date:(NSString *)date isTop:(BOOL)isTop isLatest:(BOOL)isLatest isMore:(BOOL)isMore{
    NSDictionary *dict;
    for (NSInteger i = stories.count; i > 0; i--) {
        dict = stories[i - 1];
        NSString *storyID = dict[@"id"];
        NSString *title = dict[@"title"];
        NSString *datestr = nil;
        datestr = (date == nil) ?  dict[@"date"] : date;
        // 将字典转为NSData
        NSData *listData = [NSJSONSerialization dataWithJSONObject:dict options:NSJSONWritingPrettyPrinted error:nil];
        
        // 将NSData转为字符串
        NSString *listStr = [[NSString alloc] initWithData:listData encoding:NSUTF8StringEncoding];
        
        // 拼接插入SQL语句
        NSString *insertSQL = @"INSERT INTO t_storyList (storyID, title, isCollected, isRead, isTop, isLatest, list, date, updateTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
        
        NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM t_storyList WHERE storyID = %@ and isTop = 0", storyID];
        
        if (isMore) {
            [self updateNotLatestWithStoryID:[storyID intValue]];
        }
        
        [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
            FMResultSet *result = [db executeQuery:querySQL withArgumentsInArray:nil];
            
            if (result.next) { // 结果不用的话要关闭
                [result close];
                return;
            }
            
            [db executeUpdate:insertSQL withArgumentsInArray:@[storyID, title, @(0), @(0), @(isTop), @(isLatest), listStr, datestr, @"0"]];
        }];
    }
}

/** 批量缓存详情数据 */
+ (void)cacheDetailWithIDs:(NSArray *)IDs{
    _IDs = IDs;
    NSString *urlStr = [NSString stringWithFormat:@"news/%@", IDs[_storyIDIndex]];
    
    [[NetworkingTool sharedNetworkingTool] loadDataWithUrl:urlStr parameters:nil finishedBlock:^(id responseObj, NSError *error) {
        if (error) return;
       [self cacheDetail:responseObj ID:[IDs[_storyIDIndex] intValue] justCache:YES];
        _storyIDIndex++;
        _progressText = [NSString stringWithFormat:@"%.f%%", _storyIDIndex * 100.0 / _IDs.count];
        ![SQLiteManager sharedmanager].cacheCompletedBlock ? : [SQLiteManager sharedmanager].cacheCompletedBlock(_progressText);
        
        if (_storyIDIndex == _IDs.count) {
            _storyIDIndex = 0;
            _progressText = nil;
            _IDs = nil;
            return;
        }
        [SQLiteManager cacheDetailWithIDs:IDs];
    }];
}

/** 缓存新闻详情数据到本地 */
+ (void)cacheDetail:(NSDictionary *)detailDict ID:(int)ID justCache:(BOOL)justCache{
    if (justCache) { // 仅仅是缓存的话，提前下载图片
        NSString *imgUrl = detailDict[@"image"];
        if (imgUrl) {
            [[SDWebImagePrefetcher sharedImagePrefetcher] prefetchURLs:@[imgUrl]];
        }
    }
    
    if ([self queryStoryDetailWithID:ID] != nil) return;
    
    NSString *storyID = @(ID).stringValue;
    NSString *title = detailDict[@"title"];
    
    // 将字典转为NSData
    NSData *detailData = [NSJSONSerialization dataWithJSONObject:detailDict options:NSJSONWritingPrettyPrinted error:nil];
    
    // 将NSData转为字符串
    NSString *storyDetail = [[NSString alloc] initWithData:detailData encoding:NSUTF8StringEncoding];
    
    // 拼接插入SQL语句
    NSString *insertSQL = @"INSERT INTO t_story (storyID, title, isCollected, isLiked, storyDetail) VALUES (?, ?, ?, ?, ?);";
    
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:insertSQL withArgumentsInArray:@[storyID, title, @(0), @(0), storyDetail]];
    }];
}

#pragma mark - ------------------删------------------
/** 删除顶部topStories */
+ (void)deleteTopStories{
    NSString *deleteSQL = @"delete from t_storyList where isTop = 1;";
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:deleteSQL withArgumentsInArray:nil];
    }];
}

/** 清理缓存的数据 */
+ (void)clearData{
    // 1.获取一周前的时间
    NSDate *aWeekAgoDate = [NSDate dateWithTimeIntervalSinceNow:-7 * 24 * 60 * 60];
    NSDateFormatter *fmt = [[NSDateFormatter alloc] init];
    fmt.dateFormat = @"yyyy-MM-dd HH:mm:ss";// 2016-08-26 12:25:11
    NSString *dateString = [fmt stringFromDate:aWeekAgoDate];
    
    // 2.拼接删除数据的SQL
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM t_story WHERE createTime < '%@' and isCollected = 0;", dateString];
    NSString *deleteSQL2 = [NSString stringWithFormat:@"DELETE FROM t_storyList WHERE createTime < '%@' and isCollected = 0;", dateString];
    
    // 3.执行SQL语句
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:deleteSQL withArgumentsInArray:nil];
        [db executeUpdate:deleteSQL2 withArgumentsInArray:nil];
    }];
}

#pragma mark - ------------------改------------------
/** 更新文章为非latest */
+ (void)updateNotLatestWithStoryID:(int)storyID{
    // 拼接SQL语句
    NSString *updateSQL = [NSString stringWithFormat:@"update t_storyList set isLatest = 0 where storyID = %d;", storyID];
    
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:updateSQL withArgumentsInArray:nil];
    }];
}

/** 更新文章为是否已赞状态 */
+ (void)updateIsLikedWithStoryID:(int)storyID isLiked:(BOOL)isLiked{
    // 拼接SQL语句
    NSString *updateSQL = [NSString stringWithFormat:@"update t_story set isLiked = '%d' where storyID = %d;", isLiked, storyID];
    
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:updateSQL withArgumentsInArray:nil];
    }];
}

/** 更新文章为已读状态 */
+ (void)updateReadStatusWithStoryID:(int)storyID{
    
    if ([self queryIsReadWithStoryID:storyID]) {
        return;
    }
    
    NSMutableDictionary *alreadyReadDict = [[NSUserDefaults standardUserDefaults] objectForKey:JKAlreadyReadDictKey];
    
    if (!alreadyReadDict) {
        alreadyReadDict = [NSMutableDictionary dictionary];
    }
    
    NSMutableDictionary *dict = [NSMutableDictionary dictionaryWithDictionary:alreadyReadDict];
    
    dict[@(storyID).stringValue] = @YES;
    [[NSUserDefaults standardUserDefaults] setObject:dict forKey:JKAlreadyReadDictKey];
    [[NSUserDefaults standardUserDefaults] synchronize];
    
    // 拼接SQL语句
    NSString *updateSQL = [NSString stringWithFormat:@"update t_storyList set isRead = '1' where storyID = %d;", storyID];
    
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        [db executeUpdate:updateSQL withArgumentsInArray:nil];
    }];
}

/** 批量更新文章为未收藏状态 */
+ (void)updateCollectionsCancelWithStoryIDs:(NSArray *)storyIDs{
    for (int i = 0; i < storyIDs.count; i++) {
        [self updateCollectedStatusWithStoryID:[storyIDs[i] intValue] collected:NO];
    }
}

/** 更新文章为收藏\未收藏状态 */
+ (BOOL)updateCollectedStatusWithStoryID:(int)storyID collected:(BOOL)collected{
    // 拼接SQL语句
    NSString *updateSQL1 = [NSString stringWithFormat:@"update t_storyList set isCollected = %d, updateTime = '%@' where storyID = %d and isTop = 0;", collected, [JKDateTool getDateAndTimeStrOfToday], storyID];
    NSString *updateSQL2 = [NSString stringWithFormat:@"update t_story set isCollected = %d where storyID = %d;", collected, storyID];
    
    __block BOOL success = NO;
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        success = [db executeUpdate:updateSQL1 withArgumentsInArray:nil];
        [db executeUpdate:updateSQL2 withArgumentsInArray:nil];
    }];
    return success;
}

#pragma mark - ------------------查------------------
/** 查询文章是否已赞 */
+ (BOOL)queryIsLikedWithStoryID:(int)storyID{
    // 从数据库获取收藏列表
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM t_story WHERE storyID = %d;", storyID];
    
    __block BOOL isLiked = NO;
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:querySQL withArgumentsInArray:nil];
        while (result.next) {
            NSString *like = [result stringForColumn:@"isLiked"];
            if (like == nil) continue;
            isLiked = [like boolValue];
        }
    }];
    return isLiked;
}

/** 获取收藏列表 */
+ (NSArray *)queryCollectedStoryList{
    // 从数据库获取收藏列表
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM t_storyList WHERE isCollected = 1 and isTop = 0 order by updateTime desc;"];
    
    NSMutableArray *lists = [NSMutableArray array];
    NSMutableArray *storyIDs = [NSMutableArray array];
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:querySQL withArgumentsInArray:nil];
        while (result.next) {
            NSString *list = [result stringForColumn:@"list"];
            NSString *storyID = [result stringForColumn:@"storyID"];
            if (list == nil) continue;
            if ([storyIDs containsObject:storyID]) continue;
            [storyIDs addObject:storyID];
            [lists addObject:list];
        }
    }];
    [storyIDs removeAllObjects];
    storyIDs = nil;
    return lists;
}

/** 获取首页topStories的storyID列表 */
+ (NSArray *)queryTopStoryIDs{
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM t_storyList WHERE isTop = 1 order by storyID;"];
    NSMutableArray *storyIDs = [NSMutableArray array];
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:querySQL withArgumentsInArray:nil];
        while (result.next) {
            NSString *ID = [result stringForColumn:@"storyID"];
            if (ID == nil) continue;
            [storyIDs addObject:ID];
        }
    }];
    
    return storyIDs;
}

/** 获取首页最新数据 */
+ (NSDictionary *)queryLatestStoryList{
    int today = [JKDateTool getDateOfToday];
    // 从数据库获取详情数据
    NSString *queryTodaySQL = [NSString stringWithFormat:@"SELECT * FROM t_storyList WHERE date = %d order by ID desc;", today];
    NSString *queryYesterdaySQL = [NSString stringWithFormat:@"SELECT * FROM t_storyList WHERE date = %d order by ID desc;", [JKDateTool getPreviousDayOfSpecifiedDate:today]];
    
    NSMutableArray *lists = [NSMutableArray array];
    NSMutableArray *topArr = [NSMutableArray array];
    __block NSString *dateStr = nil;
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:queryTodaySQL withArgumentsInArray:nil];
        
        while (result.next) {
            NSString *list = [result stringForColumn:@"list"];
            NSString *top = [result stringForColumn:@"isTop"];
            
            if (dateStr == nil) {
                dateStr = [result stringForColumn:@"date"];
            }
            
            if (list == nil || dateStr == nil || top == nil) continue;
            
            NSData *listData = [list dataUsingEncoding:NSUTF8StringEncoding];
            NSDictionary *listDict = [NSJSONSerialization JSONObjectWithData:listData options:NSJSONReadingMutableContainers error:nil];
            if (listDict == nil)  continue;
            
            if ([top isEqualToString:@"1"]) {
                [topArr addObject:listDict];
            }else {
                [lists addObject:listDict];
            }
        }
        if (dateStr == nil || lists.count <= 0 || topArr.count <= 0) {
            [lists removeAllObjects];
            [topArr removeAllObjects];
            dateStr = nil;
            FMResultSet *result2 = [db executeQuery:queryYesterdaySQL withArgumentsInArray:nil];
            while (result2.next) {
                NSString *list = [result2 stringForColumn:@"list"];
                NSString *top = [result2 stringForColumn:@"isTop"];
                
                if (dateStr == nil) {
                    dateStr = [result2 stringForColumn:@"date"];
                }
                
                if (list == nil || dateStr == nil || top == nil) continue;
                
                NSData *listData = [list dataUsingEncoding:NSUTF8StringEncoding];
                NSDictionary *listDict = [NSJSONSerialization JSONObjectWithData:listData options:NSJSONReadingMutableContainers error:nil];
                if (listDict == nil)  continue;
                
                if ([top isEqualToString:@"1"]) {
                    [topArr addObject:listDict];
                }else {
                    [lists addObject:listDict];
                }
            }
        }
    }];
    if (dateStr == nil || lists.count <= 0 || topArr.count <= 0) return nil;
    
    return @{JKStoryDateKey : dateStr, JKStoryListKey : lists, JKTopStoryListKey : topArr};
}

/** 指定日期的story列表 */
+ (NSDictionary *)queryStoryListWithDate:(int)date{
    // 从数据库获取指定日期的story列表
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM t_storyList WHERE date = '%d' and isTop = '0' and isLatest = '0' order by ID desc;", date];
    
    NSMutableArray *lists = [NSMutableArray array];
    __block NSString *dateStr = nil;
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:querySQL withArgumentsInArray:nil];
        
        while (result.next) {
            NSString *list = [result stringForColumn:@"list"];
            
            if (dateStr == nil) {
                dateStr = [result stringForColumn:@"date"];
            }
            
            if (list == nil || dateStr == nil) continue;
            
            NSData *listData = [list dataUsingEncoding:NSUTF8StringEncoding];
            NSDictionary *listDict = [NSJSONSerialization JSONObjectWithData:listData options:NSJSONReadingMutableContainers error:nil];
            
            if (listDict == nil) continue;
            [lists addObject:listDict];
        }
    }];
    if (lists.count <= 0 || dateStr == nil) return nil;
    
    return @{JKStoryDateKey : dateStr, JKStoryListKey : lists};
}

/** 从数据库获取详情数据 */
+ (NSDictionary *)queryStoryDetailWithID:(int)ID{
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM t_story WHERE storyID = %@;", @(ID).stringValue];
    
    __block NSDictionary *detailDict = nil;
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:querySQL withArgumentsInArray:nil];
        
        while (result.next) {
            NSString *storyDetail = [result stringForColumn:@"storyDetail"];
            
            if (storyDetail == nil) continue;
            
            NSData *detailData = [storyDetail dataUsingEncoding:NSUTF8StringEncoding];
            detailDict = [NSJSONSerialization JSONObjectWithData:detailData options:NSJSONReadingMutableContainers error:nil];
        }
    }];
    return detailDict;
}

/** 查询文章是否已读 */
+ (BOOL)queryIsReadWithStoryID:(int)storyID{
    
    NSMutableDictionary *alreadyReadDict = [[NSUserDefaults standardUserDefaults] objectForKey:JKAlreadyReadDictKey];
    if (!alreadyReadDict) {
        return NO;
    }
    
    return alreadyReadDict[@(storyID).stringValue] != nil;
    
    /* 从数据库获取详情数据
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM t_storyList WHERE storyID = %d;", storyID];
    
    __block BOOL isRead = NO;
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:querySQL withArgumentsInArray:nil];
        while (result.next) {
            NSString *read = [result stringForColumn:@"isRead"];
            if (read == nil) continue;
            isRead = [read boolValue];
        }
    }];
    
    return isRead; */
}

/** 查询文章是否已收藏 */
+ (BOOL)queryIsCollectedWithStoryID:(int)storyID{
    // 从数据库获取详情数据
    NSString *querySQL = [NSString stringWithFormat:@"SELECT * FROM t_story WHERE storyID = %d;", storyID];
    
    __block BOOL isCollected = NO;
    [[SQLiteManager sharedmanager].dbQueue inDatabase:^(FMDatabase *db) {
        FMResultSet *result = [db executeQuery:querySQL withArgumentsInArray:nil];
        while (result.next) {
            NSString *collected = [result stringForColumn:@"isCollected"];
            if (collected == nil) continue;
            isCollected = [collected boolValue];
        }
    }];
    
    return isCollected;
}
@end
